package com.training.dao;

import com.training.util.JdbcTemplate;

import java.sql.SQLException;

public class DatabaseInitializer {
    private final JdbcTemplate jdbcTemplate = new JdbcTemplate();
    
    public void initializeDatabase() {
        try {
            // 创建数据库（如果不存在）
            createDatabaseIfNotExists();
            
            // 创建产品表
            createProductTable();
            
            // 创建用户表
            createUserTable();
            
            // 插入初始数据
            insertInitialData();
            
            System.out.println("Database initialized successfully!");
        } catch (SQLException e) {
            System.err.println("Failed to initialize database: " + e.getMessage());
            e.printStackTrace();
        }
    }
    
    private void createDatabaseIfNotExists() throws SQLException {
        String createDbSql = "CREATE DATABASE IF NOT EXISTS warehouse CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci";
        jdbcTemplate.execute(createDbSql);
        
        // 使用数据库
        jdbcTemplate.execute("USE warehouse");
    }
    
    private void createProductTable() throws SQLException {
        String createTableSql = """
            CREATE TABLE IF NOT EXISTS products (
                id INT AUTO_INCREMENT PRIMARY KEY,
                name VARCHAR(255) NOT NULL,
                price DECIMAL(10, 2) NOT NULL,
                stock INT NOT NULL DEFAULT 0,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_name (name),
                INDEX idx_price (price)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """;
        
        jdbcTemplate.execute(createTableSql);
    }
    
    private void createUserTable() throws SQLException {
        String createTableSql = """
            CREATE TABLE IF NOT EXISTS users (
                id BIGINT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(50) NOT NULL UNIQUE,
                password VARCHAR(100) NOT NULL,
                real_name VARCHAR(100),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                INDEX idx_username (username)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci
            """;
        
        jdbcTemplate.execute(createTableSql);
    }
    
    private void insertInitialData() throws SQLException {
        // 插入产品初始数据
        insertInitialProducts();
        
        // 插入用户初始数据
        insertInitialUsers();
    }
    
    private void insertInitialProducts() throws SQLException {
        // 检查是否已有产品数据
        Object count = jdbcTemplate.queryForValue("SELECT COUNT(*) FROM products");
        if (count != null && ((Number) count).intValue() > 0) {
            System.out.println("Initial product data already exists, skipping insertion.");
            return;
        }
        
        // 插入初始产品数据
        String insertSql = "INSERT INTO products (name, price, stock) VALUES (?, ?, ?)";
        
        jdbcTemplate.update(insertSql, "Laptop", 999.99, 10);
        jdbcTemplate.update(insertSql, "Smartphone", 699.99, 15);
        jdbcTemplate.update(insertSql, "Tablet", 399.99, 20);
        
        System.out.println("Initial product data inserted successfully!");
    }
    
    private void insertInitialUsers() throws SQLException {
        // 检查是否已有用户数据
        Object count = jdbcTemplate.queryForValue("SELECT COUNT(*) FROM users");
        if (count != null && ((Number) count).intValue() > 0) {
            System.out.println("Initial user data already exists, skipping insertion.");
            return;
        }
        
        // 插入初始用户数据
        String insertSql = "INSERT INTO users (username, password, real_name) VALUES (?, ?, ?)";
        
        jdbcTemplate.update(insertSql, "admin", "123456", "系统管理员");
        jdbcTemplate.update(insertSql, "user", "123456", "普通用户");
        
        System.out.println("Initial user data inserted successfully!");
    }
}
